Night Owl 9

home *** CD-ROM | disk | FTP | other *** search

/ Night Owl 9 / Night Owl CD-ROM (NOPV9) (Night Owl Publisher) (1993).ISO / 032a / acctxt2.zip / COMPILED.TXT < prev next >

Wrap

Text File | 1993-01-15 | 55KB | 1,360 lines

This document contains a summary of issues regarding Microsoft Access which have been reported to Microsoft Product Support. Those issues which contain a number in the format of Q##### in the header have been confirmed and are documented in the Microsoft Knowledgebase. All others issues are still being researched or written at the time this document was posted. The Microsoft Knowledgebase can be accessed at any time on GO MSKB at the prompt. ------------------------------------------------------------------------ Table of Contents Setup Issues Q88914 Running MS-DOS SHARE with Windows for Workgroups Q90105 Couldn't Find SYSTEM.MDA or UTILITY.MDA File Q90863 Older Versions of Shared DLLs Cause Problems with Access Q92639 Cannot Install Cue Cards Without Installing Help Q92824 Custom Setup May Show Zero or Negative Disk Space Needed Q93694 Setup Problem Regarding the COMMDLG.DLL File and MS Access Unconfirmed: Some TSR's and other programs may cause Setup to fail Article Pending: Check command in Stacker 3.0 reports errors in .mdb files Print Merge Issues Q93427 Using Data From Access In A Word For Windows Print Merge Article Pending: Sending The Current Record To Word For Windows Via DDE Unconfirmed: Word Print Merge With Fail if the First Field Is NULL Unconfirmed: Save As an embedded Word Document Causes File to Be Deleted ODBC Issues Q90151 Windows for Workgroups, Novell, SQL Server, and Access Unconfirmed: GP Fault Using an Out of Date ODBC Driver Unconfirmed: INF: Error MSG: ODBC - couldn't find ODBC.DLL Unconfirmed: ErrMsg: Couldn't execute query; couldn't find linked table Q93145 How Access Handles Logins to Attached SQL Tables Q88655 How Access Uses SQL Server Connections DDE & OLE Issues Q94108 Parameter Queries Are Not Supported As DDE Topics Topics supported in Access are listed in the README.TXT file Article Pending: DDE timeout when DDE module called from a DDE client Btrieve Issues Q93685 Couldn't find Object <tablename> Article Pending: Corrupt Error Message with Attached Btreive Table Article Pending: User is Unable to Attach to a Btrieve Database. ------------------------------------------------------------------------ ------------------------------------------------------------------------ Setup Issues ------------------------------------------------------------------------ Q88914 Running MS-DOS SHARE with Windows for Workgroups ------------------------------------------------------------------------ Summary: During the installation of Microsoft Access, Setup automatically inserts the following MS-DOS command in the AUTOEXEC.BAT file: <MS-DOS directory>\SHARE.EXE /L:500 If you are running the Microsoft Windows for Workgroups operating system, it is recommended that this SHARE command be removed. More Information: Windows for Workgroups has its own sharing mechanism (VSHARE.386), which is loaded with Windows (there is an entry for it in the SYSTEM.INI file). If you load SHARE before running Windows for Workgroups, VSHARE will not load. Everything should work correctly, but the number of locks available to you will be limited by the number specified when SHARE was loaded (determined by the /L parameter--the default is 20). VSHARE, on the other hand, dynamically allocates the number of locks available on demand. The number of available locks is very important if your Windows for Workgroups machine is going to act as a server. ------------------------------------------------------------------------ Q90105 Couldn't Find SYSTEM.MDA or UTILITY.MDA File ------------------------------------------------------------------------ Summary: When Microsoft Access is started, one or both of the following error messages may be displayed: Couldn't find file 'SYSTEM.MDA' Couldn't find file 'UTILITY.MDA' These errors occur if the SystemDB and UtilityDB settings in the [Options] section of the MSACCESS.INI file point to a directory that does not contain the SYSTEM.MDA or UTILITY.MDA file. These errors also occur if the directory points to a share in which there is no connection. If the directory does not exist, the following error message is displayed: Invalid Path 'pathname' More Information: A good way to manage the MSACCESS.INI file is to keep backup copies that pertain to particular setups. Have one setup for starting in a workgroup on a network and have another that allows for starting on a local machine. To change setups, copy the appropriate backup .INI file over the MSACCESS.INI file. ------------------------------------------------------------------------ Q90863 Older Versions of Shared DLLs Cause Problems with Access ------------------------------------------------------------------------ Summary: Shared dynamic-link libraries (DLLs) in your system older than those supplied with Microsoft Access version 1.0 or Microsoft Windows version 3.1 can cause unexpected errors with Microsoft Access. To avoid problems, make sure that all shared DLLs are at least as current as those supplied with Microsoft Windows operating system version 3.1 or Microsoft Access version 1.0, and that they are all located in the WINDOWS\SYSTEM subdirectory. More Information: The shared DLLs used by Microsoft Access are: COMMDLG.DLL OLECLI.DLL OLESRV.DLL DDEML.DLL SHELL.DLL VER.DLL (These DLLs are also used by Windows-based applications other than Microsoft Access.) Microsoft Access supplies the same DLL versions as Windows 3.1, and it requires these (or newer) versions for correct operation; older versions can cause errors. Microsoft Access and Windows copy shared DLLs into the WINDOWS\SYSTEM subdirectory, and that is where Microsoft Access first looks for them. If it doesn't find one or more of the shared DLLs, it looks in the directory where Microsoft Access is installed, and then in the current directory. Often, Microsoft Access finds outdated or incorrect DLLs because third-party software with old or foreign DLLs has been installed or reinstalled, overwriting the correct DLLs. Some third-party software packages copy DLLs into directories other than WINDOWS\SYSTEM, creating multiple copies and making the problem harder to track and resolve. Microsoft Access will not reload DLLs previously loaded by another software package; it will only load shared DLLs not already loaded. If the previously loaded DLLs are incorrect, they can cause problems with Microsoft Access. If the Microsoft Access Setup program fails to locate a shared DLL, or detects an older version of one, it issues an error message such as: Outdated XXXX.DLL found. Please reinstall MSAccess. -or- Can't find XXXX.DLL. ------------------------------------------------------------------------ Q92639 Cannot Install Cue Cards Without Installing Help ------------------------------------------------------------------------ Summary: SYMPTOMS During a custom installation, you can select and deselect options through the Microsoft Access Setup Options dialog. If you deselect "Help," the size of the "Cue Cards" files increases from 1688 K to 5112 K. However, the total disk "Space required" does not increase and the size of "Help" does not decrease from 3424 K to 0 K. CAUSE You must have Help loaded in order to use Cue Cards. Therefore, when you deselect Help, the size of the Cue Cards files increases to include the size of the Help files. "Help" and "Cue Cards" are separate selections because it is possible to install and use the Help files without installing the Cue Cards files. ------------------------------------------------------------------------ Q92824 Custom Setup May Show Zero or Negative Disk Space Needed ------------------------------------------------------------------------ Summary: The amount of disk space required for each option does not display a consistent number when running a custom installation. More Information: The setup program calculates and displays the total space that you will need, it does not just display a flat number. If you already have the same version installed the option will display a zero for required disk space. If a previous version of one of the options was much greater, the required disk space will display a negative number. An example of this would be if you added more data or objects to the NWIND.MDB. In the Sample Apps Disk requirement you would notice a negative number. Steps to Reproduce Behavior --------------------------- 1. Run the setup program for Microsoft Access from Disk 1. 2. Choose Custom Installation. 3. Notice the required disk space for the options: some may be zero or negative. ------------------------------------------------------------------------ Q93694 Setup Problem Regarding the COMMDLG.DLL File and MS Access ------------------------------------------------------------------------ SYMPTOMS The following error message dialog may appear when trying to open a database: CAUSE The following error message is displayed: Outdated 'COMMDLG.DLL' Please Re-install Microsoft Access. RESOLUTION 1. You most likely have more than one version of the COMMDLG.DLL on your computer. You need to delete or rename duplicate versions of COMMDLG.DLL. 2. Make sure the COMMDLG.DLL in the windows system sub-directory is newer than: 03/10/92 for Windows 3.1 or 10/01/92 for Windows for Workgroups or 10/25/92 for Microsoft Access 3. You are using a third party non-compatible COMMDLG.DLL More Information: To search for all occurrences of COMMDLG.DLL, perform the following steps: 1. Exit Windows. 2. Search for any other copies of COMMDLG.DLL. The following command at the DOS prompt will locate all copies on drive C. You will need to do this for each drive that is located on your path statement in your AUTOEXEC.BAT file; the drive where your windows directory is located; and the drive that you have Microsoft Access installed. C:\ cd\ C:\ DIR COMMDLG.DLL /s ONLY one should be on your disk in the windows/system directory. If you have one or two copies that have been installed elsewhere by other applications, they should be deleted or renamed. How to install a new COMMDLG.DLL -------------------------------- If the user has deleted all duplicates of COMMDLG.DLL and still gets the error in Microsoft Access, the user can copy COMMDLG.DL_ from disk 1 of the Microsoft Access retail package to their hard disk, then copy EXPAND.EXE from disk 3 of their WINDOWS 3.1 diskettes. COMMDLG.DL_ can be expanded using the expand utility. Care should be taken not to copy COMMDLG.DL$ from the Microsoft Access diskettes. This file can only be decompressed with the Microsoft Access Setup. The syntax for using EXPAND.EXE is: c:\<path>\EXPAND <path>\COMMDLG.DL_ c:\windows\system\COMMDLG.DLL Other problems -------------- A situation can arise during setup when it tries to update the COMMDLG.DLL but returns an error that it can't get access to the file. In most cases, this means that some other application is running that is using the COMMDLG.DLL and therefore Microsoft Access setup can not update it. The solution is to make sure no applications are running in windows at the time of setup. To do this, move all icons from the STARTUP group into a temporary group (in Program Manager). Also, edit the WIN.INI file and clear both the 'run' and 'load' lines so that they read: [windows] RUN= LOAD= It might also be necessary to edit the AUTOEXEC.BAT to look for conflicting TSR's, although this would be the least likely cause. ------------------------------------------------------------------------ Unconfirmed: Some TSR's and other programs may cause Setup to fail ------------------------------------------------------------------------ Summary: During the installation of Microsoft Access, Setup may fail while trying to copy the file: README.TX$ Common causes for failure at this point generally are related to the use of TSR programs loaded at DOS level and/or similar programs loaded in Windows directly. To check this remove all unnecessary TSR's and Drivers from the CONFIG.SYS and AUTOEXEC.BAT files, remove Icons from the Startup Group in Program Manager, and remove items from the Load= and Run= lines in the WIN.INI file. More Information: Known programs that have repeatedly caused this kind of behavior include: APPEND.EXE a DOS TSR loaded in AUTOEXEC.BAT file SUBST.EXE a DOS TSR loaded in AUTOEXEC.BAT file BILLMNDR.EXE a Auto bill reminder loaded with Quicken by Intuit Software. This can reside on the LOAD= line of the WIN.INI ------------------------------------------------------------------------ Article Pending: Check command in Stacker 3.0 reports errors in .mdb files ------------------------------------------------------------------------ The "lost sector groups" errors being reported by "CHECK /=D /F" are 100% benign. The Access files are not corrupt in any way. There have been several reports of Access .mdb files getting corrupted when running Access on machines running Stacker 3.0. The symptom is that running Stacker's "check /=D /F" command reports that the .mdb file is corrupt and must be deleted. The undocumented CHECK switch "/=D" is a low level internal debugging tool for use by the developers at Stac Electronics. When used with "/=D /F", CHECK is very zealous about reporting *all* possible problems, no matter how benign. It will sometimes return messages that imply that files are corrupted when they really aren't. This is what is happening with Access files. "Lost sector group" error messages from "CHECK /=D /F" are not real errors, and should be completely ignored. Stac Electronics and Microsoft are actively working with *one* customer that has reported an occasionally reproducible case of data corruption when his Access .mdb file is on a Stacker 3.0 volume. This has only been reported by one person, and only while running Stacker 3.0. In spite of what has been reported in a couple of trade magazines, we are not currently aware of ANY bugs that would cause data corruption in Access databases on Stacked drives. Anyone who has experienced any case of Access reporting that his .mdb is corrupted should report it to Access PSS via the Access forum (GO MSACCESS). We take reports of these problems VERY seriously, and will do whatever is necessary to track them down. This problem is not unique to Access. It has been reported against other programs as well and is not exclusive to database products. ------------------------------------------------------------------------ Print Merge Issues ------------------------------------------------------------------------ Q93427 Using Data From Access In A Word For Windows Print Merge ------------------------------------------------------------------------ Summary: Microsoft Word for Windows does not supply a converter for Microsoft Access database files. To use data from a Microsoft Access database in Microsoft Word for Windows, the data must be converted to a format which Microsoft Word for Windows recognizes. This can be done by exporting the data from Microsoft Access as text, or copying tables and queries from Microsoft Access and pasting them into a Microsoft Word for Windows document. This article described the steps to create a data file in Microsoft Word for Windows using each of the methods described above. More Information: A data file is a comma or tab separated file that contains information that can be used in Microsoft Word for Windows for a print or mail merge. A field name in Microsoft Word for Windows cannot contain more than 20 characters. A field name must begin with a letter and can contain only letters, numbers, and the underline character (_). For example: FirstName, LastName, Address, City, State, Postal_Code Randy, Johnson, 123 West St., Baker, OR, 97445 Jennifer, Smith, 5 Circle Court, Yorkshire, WI, 34507 Method One: Copying and Pasting -------------------------------- 1. From the Database window, select the table or query that contains the information you want. 2. Choose Copy from the Edit menu. 3. Activate Microsoft Word for Windows and open a new document. 4. From the Edit menu, select Paste. Note: If there are spaces in your field name you will need to delete them once you have pasted the data into Microsoft Word for Windows. 5. Save this document as MyData.Doc. Because this method does not place each field inside quotation marks, you may encounter errors if any of the fields contain tabs. Method Two: Using The Transfer Text Macro Action ------------------------------------------------ 1. Open a new macro from within Access. 2. Add the macro action TransferText with the following arguments: Transfer Type: Export Delimited Specification Name: <leave blank> Table Name: <name of the table or query you wish to export.> File Name: <valid DOS file name.> (Example: c:\winword\mydata.txt) Has Field Name: Yes This is a one time export. If the data in your database changes you will need to re-export it, overwriting the original file. 3. Perform the following steps in Microsoft Word for Windows: a. Open the main document. b. Choose Print Merge from the File menu. c. Choose the Attach Data File button. d. Select the exported text file that you created in step 2. Note: if you did not specify a full path name for the text file, it will be in the same directory as your Microsoft Access database. Now you can use the merge features of Microsoft Word for Windows: Insert Merge Fields, Edit Data Field, Check, and Print Merge. The check button will make a pass through the data document and check for errors, such as those caused by commas or tabs stored in the Microsoft Access fields. ------------------------------------------------------------------------ Article Pending: Sending The Current Record To Word For Windows Via DDE ------------------------------------------------------------------------ Summary: This article describes the steps for creating a form which allows the user to press a button to send the current record to Microsoft Word for Windows. The data sent is merged into a pre-written letter and printed. The article assumes that you understand Dynamic Data Exchange,(DDE), setting bookmarks in Word for Windows, and creating modules in Microsoft Access. More Information: Step One: Create the Winword Document ------------------------------------- 1) Start Winword and open a new document. 2) Type in the following: CompanyName Address City, Region, PostalCode Country Dear ContactName, NorthWind Traders would like to thank you for your business during the past year. Enclosed you will find several samples of new products that we are excited to announce. Sincerely, NorthWind Traders. Note: Winword will fail if the field names contain spaces when attempting to complete the merge. Plus if you copy the text into a Winword document, be sure to remove the tabs. 3) Save this document as DDEMERGE.DOC. 4) To create the bookmarks, highlight CompanyName and choose Bookmark from the Insert menu. Name the Bookmark "CompanyName", without quotes. 5) Repeats these steps, creating bookmarks for the fields: Address, City, Region, PostalCode, Country, and ContactName. Step Two: Create The Access Basic Modules ========================================= 1) Open the example database NWIND.MDB. (One of the following modules uses the function STARTAPP() which is located in the module Introduction to Programming. 2) Create a new module called Print Merge. 3) Place the following statement in the (declarations) section: Dim Mergechan As Integer 4) Create a new function called Initiate_Word () Function Initiate_Word () Dim Chan As Variant Dim WordTopics As Variant Chan = StartApp("Winword", "System") On Error GoTo AlertUser: WordTopics = DDERequest(Chan, "Topics") If InStr(1, WordTopics, "DDEMERGE.DOC") = 0 Then DDEExecute Chan, "[FILEOPEN(""DDEMERGE.DOC"")]" End If DDETerminate Chan Mergechan = DDEInitiate("Winword", "DDEMERGE.DOC") Exit Function AlertUser: MsgBox "Access is unable to initiate a DDE channel with the document DDETEST.DOC" Resume Next End Function 5) Create a new function called Send_Record() Function Send_Record () Dim Chan As Variant Dim ControlName As Control Dim BookMarks As String On Error GoTo CatchBlanks: DDEPoke Mergechan, "CompanyName", Forms![Customers]![Company Name] DDEPoke Mergechan, "ContactName", Forms![Customers]![Contact Name] DDEPoke Mergechan, "Address", Forms![Customers]![Address] DDEPoke Mergechan, "City", Forms![Customers]![City] DDEPoke Mergechan, "Region", Forms![Customers]![Region] DDEPoke Mergechan, "PostalCode", Forms![Customers]![Postal Code] DDEExecute Mergechan, "[FilePrint]" Exit Function CatchBlanks: If MsgBox("One of these fields is blank. Would you like to continue?", 52) = 6 Then Resume Next Else Exit Function End If End Function Note: Each of the DDEPoke statements should be on one line in your function. They are split in this article for readability. 6) Create a function called Terminate_MergeChan(): Function Terminate_MergeChan () DDETerminate MergeChan End Function 7) Choose Compile All from the Run menu and then close and save the module. Step Three: Create the Form ========================================= 1) Open the form [Customers] in design mode. 2) Set the OnOpen property of the form to: =Initiate_Word() 3) Set the OnClose property of the form to: =Terminate_MergeChan() 4) Add a new button to the Customers form. 5) Set the Caption property of the button to: Print Letter. 6) Set the OnPush property of the button to: =Send_Record() 7) Save the form and switch to browse mode. Click on the Print Letter button. The current record will be sent to Word for Windows, merged into the document DDEMERGE.DOC and then printed. ------------------------------------------------------------------------ Unconfirmed: Word Print Merge With Fail if the First Field Is NULL ------------------------------------------------------------------------ If the first field in any record is NULL, Word will fill the merge fields with incorrect data or fail to merge that record. The simplest work-around is to add a counter to the Access table so that there is always data in the first field. We are working with the Word group to confirm and resolve this issue. ------------------------------------------------------------------------ Unconfirmed: Save As an embedded Word Document Causes File to Be Deleted ------------------------------------------------------------------------ If you try to "Save As" a Word document that is embedded in an Access table as an OLE field to an already existing DOS file, your DOS file is deleted and NO new file is created, thus you have NO local copy of the file. Note that the file in OLE field is Not affected. More information and Work Around: When editing a document that is embedded in an Access table you might want to save it as a DOS file. To do that you select "Save As" from File menu of Word. In the case that the DOS file already exists, Word will give you a warning and will delete the existing DOS file BUT will not save the new version in its place. There are two solutions: Either "Save As" twice (the first time the file is going to be deleted and the second time created again). Or use a non existing file name, for example you might first save your document as "mydoc.doc" then "mydoc2.doc" and so on. Note again, that the embedded Word document is not affected. ------------------------------------------------------------------------ ODBC Issues ------------------------------------------------------------------------ Q90151 Windows for Workgroups, Novell, SQL Server, and Access ------------------------------------------------------------------------ Summary: If you are running SQL Server on a NetWare network and try to add Access and Windows for Workgroups, you will not be able to talk to SQL Server from Access version 1.0. Windows for Workgroups and SQL Server have NetWare support, but the two are currently incompatible. More Information: Named pipes (the communication protocol used by SQL Server) are serviced from NETAPI.DLL. The Windows for Workgroups NETAPI.DLL overrides the Novell NETAPI.DLL, so communication with SQL Server is broken. ------------------------------------------------------------------------ Unconfirmed: GP Fault Using an Out of Date ODBC Driver ------------------------------------------------------------------------ Summary: SYMPTOMS Access will give you a "General Protection Fault (GPF) in ODBC.DLL when using an out of date ODBC.DLL driver. CAUSE The problem is that when Access initiates an ODBC connection, ODBC does not check the version of the ODBC driver. The ODBC setup program will only replace an older version of the ODBC.DLL with a newer version if the file resides in the WINDOWS\SYSTEM sub-directory. If the file is found elsewhere, ODBC setup will not go out and replace. This could cause a problem if the directory where the older .DLL resides is found in the path. RESOLUTION Rename or delete the old ODBC.DLL and run the ODBC setup program from the setup disks that come with Access. This will install an up-to-date ODBC.DLL file. ------------------------------------------------------------------------ Unconfirmed: INF: Error MSG: ODBC - couldn't find ODBC.DLL ------------------------------------------------------------------------ Summary: If you receive the error message: ODBC - couldn't find ODBC.DLL The ODBC.DLL file you are using may be old, you may have duplicate ODBC.DLL files on your system and the wrong one is being used, or the ODBC.DLL file you are using may be damaged or corrupted. More Information: It is possible that there are other situations which may indirectly cause this error message to display. The other things which you will need to check to troubleshoot this problem include: 1. Use File Manager to search for multiple, corrupted, or outdated copies of these files: DBNMP3.DLL SQLSRVR.DLL NETAPI.DLL COMMDLG.DLL 2. Make sure that the files listed above are in their appropriate locations. All of the files except NETAPI.DLL are usually installed in the \WINDOWS\SYSTEM directory. The NETAPI.DLL file is a network specific driver and is usually located in the network's program directory, which should be in the path. 3. Use another application, such as Q+E, SAF, or PowerBuilder, on the same machine to attempt to attach to the same data source. If you cannot access the data source from another application then it is probably a network problem, such as NETAPI.DLL, or a problem with the server, not with Access or ODBC. 4. Attempt to attach to another data source, if available, such as another SQL server. If you can attach to another server, then the original server may be down or you may need to re-configure the entry for that server using the ODBC Administration Utility. 5. Attempt to attach to the same data source from another machine. If you couldn't access the SQL server from another machine that would indicate a network wide problem or that the server may be down or not communicating. 6. Try increasing your ODBC time-out settings in the ODBC section of the MSACCESS.INI file. 7. Check with the system administrator to see if the SQL server is up, available for transactions, and operating properly. 8. Reinstall ODBC after performing a clean boot and renaming the ODBC.DLL, DBNMP3.DLL, and SQLSRVR.DLL files. ------------------------------------------------------------------------ Unconfirmed: ErrMsg: Couldn't execute query; couldn't find linked table ------------------------------------------------------------------------ Summary: If you receive the error messages: Couldn't execute query; couldn't find linked table' Invalid object name TABLE_NAME TABLE_NAME is the name of the table you are attempting to use. The most likely cause of this error is an incorrect entry in the [SQLSERVER] section of your WIN.INI file. To correct this situation you need to verify that the [SQLSERVER] section of your WIN.INI file has the correct entry for the server you are attempting to attach to. More Information: The [SQLSERVER] section of your WIN.INI file should look like this: [SQLSERVER] MYSERVER=dbnmp3,\\MYSERVER\PIPE\SQL\QUERY YOURSRVR=dbnmp3,\\YOURSRVR\PIPE\SQL\QUERY If the server name in the entry for the server you are trying to access is incorrect or if the entry has additional spaces in it this error will occur. ------------------------------------------------------------------------ Q93145 How Access Handles Logins to Attached SQL Tables ------------------------------------------------------------------------ Summary: How Microsoft Access handles a login to an attached SQL table depends on how the table was originally attached, and whether the login and password were saved with the table information. It may also depend on who attached the SQL table, what rights that person has to that table, what rights you have to that table, and where the tables are located (different servers, different databases, same database). More Information: If the "Save login ID and password locally" option was enabled when a SQL table is attached, you will not be prompted for a login ID and password. Instead, you automatically receive the rights to each table that are appropriate for the login ID used when the table was attached. If this option was not enabled, you are prompted for a login ID and password once for each unique data source, and you receive the rights to each table that are appropriate for the login ID that you use. NOTE: A unique data source is composed of the unique combination of a SQL server and a database on that server. So, if you attach multiple SQL tables and use different login IDs with different rights for each table, but do not enable the option to save the login and password information, the next time you access the tables, the login ID and password you supply for each data source will determine the authority you have to the tables from that data source. ------------------------------------------------------------------------ Q88655 How Access Uses SQL Server Connections ------------------------------------------------------------------------ Summary: This article describes how Access uses connections to SQL Server and how application developers can minimize use of these connections. This article assumes the reader is highly knowledgeable about the low level interaction between applications and SQL Server. More Information: The conventional application that accesses a server does it in a simple single-tasking manner, requiring a single connection to the server. Access, however, is not the typical conventional server front-end. Many of its features, including updatable views (dynasets), joins between local and server data, execution of complex expressions that the server may not be able to handle, and seamless transition from one server to another, require Access to obtain more connections than the conventional front-end needs. Although some servers, such as Microsoft SQL Server, are not too stingy on giving out connections, there are servers that place strict limits on the number of connections an application can open. (In some installations, SQL Server also places absolute restrictions on connections.) When designing applications for the more restrictive servers, it is important to understand how Access uses connections. Connections are opened by Access when it needs to execute a query on the server (which it must do to build dynasets or static views), obtain the data in a dynaset, or update data on the server. These connections can be categorized into two types: connections needed to build the working set, and connections needed to service dynasets. Both types of views available in Access (static views and dynasets) are built by executing a query (called a "local" query to differentiate it from other types of queries discussed below). This local query processes Access and foreign ISAM data (both locally and file server based) and remote server data, and pulls it into a temporary table that represents the view. To build static views, the actual data is stored in the temporary table, whereas to build dynasets, pointers to the actual data are stored. When server data is included in the local query, execution of the query involves asking the server (or servers) for data, which is done by opening connections and sending queries to the server. Each query sent to the server requires that a connection be opened. If more than one query is to be sent to the server, a single connection could be used and the queries executed serially. However, this would typically require the user to wait for all of the server queries to complete before any data would appear. Therefore, Access executes the server queries in parallel, requiring a connection for each. The number of queries sent to a server may not be entirely obvious. Much of the time, access to all of the remote base tables will be combined into a single query, if all of the base tables reside on the same server. In the worst case, each server base table referenced by the local query will require a connection. There are several factors that could compel Access to split a local query into several server queries: - If server data is joined to data from other sources (that is, local, foreign ISAM, and other servers), the Access optimizer may decide that it is more efficient not to combine references to two tables on the same server into a single query. For example, if the Customers and Order Items tables are on a server and the Orders table is in a local Microsoft Access database, then it is likely that the optimizer will decide to ask for the Customers and Order Items tables separately, rather than asking for the cross-product of the two, to join to the Orders table. As you can tell from this example, this is unlikely to happen in a real scenario. - If an expression involved in the query cannot be executed on the server (either because the server doesn't support it or because the semantics provided by the server differ greatly with those provided by Access), the expression will be executed locally. An extreme example of this is a local query with a restriction that calls a user-defined function. Although this may not require separating remote tables into separate server queries, it will if the expression that isn't executable on the server is somehow involved with the join between the two tables. Note that in Access, expressions containing a conjunction that can be executed on the server will be executed locally in their entirety. These expressions are not split to execute part locally and part on the server. - The parameters used to attach tables on the same server to an Access database differ. For example, if two tables are in different databases, or if they are being accessed using different user names and passwords, then separate connections will be required to execute a local query referencing both of them. Connections opened in the execution of a local query will be kept open until the working set containing the queries is closed. Connections are also opened in support of dynasets. But, unlike the server queries sent during the execution of a local query, the queries sent to the server in support of a dynaset are executed entirely and very quickly. This is because they are used to fill portions of the dynaset with data (given the pointers to the data provided by the local query), and to do updates to the server. Therefore, Access opens only one connection per attached server, provided that the link attachment information is the same for each server tables. As a result of the above rules, when an Access query is executed, it typically requires only one (for a static view or two (for a dynaset) connection. This doesn't represent an extravagant use of server connections, but good applications could involve the execution of many Access queries that could occupy many server connections very quickly. There are some steps the designer can take to minimize the use of connections: - Reduce the number of queries required by the application. Each form, subform, data sheet, report, and list box requires a query, and each of these queries that accesses server data will require at least one or two connections. Close forms, data sheets, and reports as soon as they are no longer needed. Also, bring server data to a local database where practical, especially for filling list boxes. - Avoid queries that join server data to local data to server data, as described above. These type of queries will most likely be executed using a connection for each server table. - Avoid expressions that must be executed locally when joining server data together. Each of the tables involved with such a join expression, or that is restricted by a WHERE clause containing such an expression, will need to be executed locally. It should be noted that separating expressions into separate query objects will not help alleviate this problem, because the optimizer combines all query objects in a single query before execution for efficiency. - Use static working sets when seeing other users' changes, or when making changes yourself is not required. This will avoid the connections needed to support dynasets. With an understanding of how Access uses connections, and with prudent application design, you should not run out of server connections. ------------------------------------------------------------------------ DDE & OLE Issues ------------------------------------------------------------------------ Q94108 Parameter Queries Are Not Supported As DDE Topics ------------------------------------------------------------------------ Summary: Microsoft Access does not support Parameter Queries via DDE. Parameter driven queries require parameters for which Microsoft Access has no mechanism to receive via DDE. More Information: Attempts to initiate a DDE channel with a parameter query will fail. The resulting error message is dependent upon the DDE Client. In Microsoft Visual Basic the user will see: "No foreign application responded to DDE Initiate". In Microsoft Excel the error message is: "Remote Data not Accessible. Start 'MSACCESS.EXE'?" One work around is to link an Microsoft Access form to the DDE client. This link will transfer the parameter to Microsoft Access. Then the modify the query to refer to the form for the parameter. The steps to use this method with Microsoft Excel follow: A. Create the DDE Link ------------------- 1. Start Microsoft Excel and open a new Macro sheet. 2. In cell A1 enter "DAIR" without quotation marks. 3. Save the macro sheet as DDETEST.XLM. 4. Select cell A1 and choose Copy from the Edit menu. 5. Start Microsoft Access and open the Northwind Traders database, NWIND.MDB. 6. Create a new, unbound form. 7. Choose Paste Special from the Edit menu. 8. Select Text in the Data Type list box and then click Paste Link. This creates a Text box with the formula: =DDE("Excel","C:\ACCESS\DDETEST.XLM","R1C1") 9. If the Properties box is not available choose Properties from the View menu. 10. Select the text box created in step 7 and change the Control Name property to: DDE Parameter. 11. Save the form as [DDE Link Form]. B. Modify the Query ---------------- 1. Open the query [Products By Category (Parameter)] in design mode. 2. Select Parameters from the Query menu and delete the criteria [Enter a Category ID]. 3. Under the field [Category ID] in the query grid replace the existing criteria with: =Forms![DDE Link Form]![DDE Parameter] 4. To test the query verify that the macro sheet DDETEST.XLM is open in Microsoft Excel and that the form [DDE Link Form] is in Browse mode. Run the query and verify that the products are in the category "DAIR". C. Create a Macro to Open the Form ---------------------------------- 1. Create a new macro. 2. Add the following macro action: OpenForm Form Name: DDE Link Form View: Form 3. Save this macro as "Open Form", without quotes. D. Create a Macro to Run the Query ---------------------------------- 1. Create a new macro. 2. Add the following macro action: OpenQuery Query Name: Products by Categories (Parameter) View: Datasheet 3. Save this macro group as "Run Query", without quotes. E. Create the DDE Macro -------------------- 1. In the macro sheet DDETEST.XLM enter the following macro: Cell Command --------------- A1 DAIR A2 A3 chan=INITIATE("MSAccess","System") A4 =EXECUTE(chan,"Open Form") A5 =APP.ACTIVATE("Microsoft Access") A6 =SEND.KEYS("{F9}") A7 =TERMINATE(chan) A8 =ON.TIME(0.010,Run_Query) A9 A10 chan=INITIATE("MSAccess","System") A11 =EXECUTE(chan,"Run Query") A12 =TERMINATE(chan) A13 =RETURN() 2. Place your cursor in cell A10, choose Define Name from the Formula menu, enter Run_Query in the Name: box. Click the option Command and then choose OK. This gives the name Run_Query to the second macro. 3. Close all forms, macros, and queries in Access. 4. To run the macro place your cursor in cell A3, choose Run from the Macro menu, and then press OK. The Excel macro will run much more quickly if you set the DDE TimeOut option in Microsoft Access to 1 second. Notes: Why are SendKeys used? Excel has instructed Microsoft Access to open the form[DDE Link Form]. When Microsoft Access opens the form it will attempt to update the DDE link in the control [DDE Parameter]. However, Excel will not process any requests until it receives notification that Microsoft Access has successfully completed the macro. Microsoft Access will not finish executing until it successfully updates the link to Excel. Eventually Microsoft Access will time-out and display #Error. The SendKeys command sends the key sequence F9 which refreshes the form. How would you get this information to Excel? If you need to transfer the result of the query to Excel change the query to a Make Table query. You will then be able to use the DDE Request function to retrieve the data in the table from Excel. Reference: The DDE Server Topics supported by Microsoft Access are documented in the file README.TXT which in installed in your Microsoft Access directory. For information on parameter queries refer to the Microsoft Access User's Guide, Chapter 7 Designing Action Queries and Parameter Queries, pp.178-181 For information on creating DDE links in forms and reports refer to the Microsoft Access User's Guide, Chapter 13 Using Picture, Graphs, and Other Objects, pp.337-339 ------------------------------------------------------------------------ Topics supported in Access are listed in the README.TXT file ------------------------------------------------------------------------ The topics that are supported in Access as a DDE server are listed in the README.TXT file that is installed in the Access directory. The title of the section is: Using Microsoft Access as a DDE Server. This same information is also documented in Q89586 in the Microsoft Knowledge Base. ------------------------------------------------------------------------ Article Pending: DDE timeout when DDE module called from a DDE client ------------------------------------------------------------------------ Access macros can run modules that perform DDE statements. If this macro is called from a DDE client, such as Microsoft Visual Basic (VB) or Excel the operation will be performed but no data will be returned to Access and Access will return a DDE time out error. Example: A VB application that initiates a channel to Access and tells it to run a macro. This macro initiates a channel to Excel to get some information off of a spreadsheet and then imports the data into a table. The macro will run correctly from Access and places the data in the Access table. But when VB runs the macro, the channel to Excel is initiated, but Access pauses when it requests the data from Excel, and eventually gets a DDE Timeout. No data is transferred back to Access. There is currently no direct work around for this issue. But often the issue can be resolved by looking at how the program is being run. In this example, Visual Basic is being used as a front end to Access. The macro could be run directly from Access and if there are other steps that VB needs to perform the DDE channel could be turned around, making the VB application the server and calling the code from Access or perhaps the commands could all be shifted over to Access Basic. ------------------------------------------------------------------------ Btrieve Issues ------------------------------------------------------------------------ Q93685 Couldn't find Object <tablename> with Btrieve files ------------------------------------------------------------------------ Summary: A number of people have reported receiving the error message: Couldn't find object '<table name.' when attempting to import or attach to Btrieve files. This article explains how to identify and fix this problem. More Information: Microsoft Access requires the Xtrieve-format data dictionary files, FILE.DDF, INDEX.DDF, and FIELDS.DDF, to be able to use Btrieve files. FILE.DDF contains the DOS file name of each data table in the dictionary. These file names can be in any of the valid four DOS formats: 1. File Only ("mydata.dat") 2. Relative Path ("..\dev\test\mydata.dat") 3. Fully Qualified ("c:\tools\dev\test\mydata.dat") 4. Universal Naming Convention, UNC ("\\myserver\users\joe\tools\dev\test\mydata.dat") When importing or attaching to a Btrieve file, Microsoft Access retrieves the DOS filename from FILE.DDF. It then attempts to open this file. If the file doesn't exist in the location specified, you will receive the error: Couldn't find object In the case of the first 2 examples, the ability to open the file is dependent on the current directory being correct. For example #1, MYDATA.DAT must exist in the current directory. For example #2, MYDATA.DAT, the "..\DEV\TEST" directory must exist relative to the current directory, and MYDATA.DAT must exist in it. Exactly which directory is the current directory is complicated by the fact that under Windows, all applications share the same environment, and therefore the same current directory. If you change the current directory in Excel, for example by doing a File Open and changing directories to open a file, the current directory for Microsoft Access is also changed. So, the first step to fixing the problem is to find out what the paths are in FILE.DDF. You can do this by opening FILE.DDF in WRITE, the word processor provided with Windows. When asked if you want to convert this file to WRITE format, specify No Conversion. You will see a bunch of characters that look like a box. Scroll to the end of the file. There you will see the DOS filenames of your Btrieve data files. The extension is usually .DAT, but doesn't have to be. WARNING: DO NOT save the file after you are finished viewing it with WRITE. If you save this file, it will become corrupt and not repairable. Now that you have the file names, there are 2 things you can do to fix the problem: 1. Move the files into the correct location(s). This is the easiest fix, and the one we recommend. 2. Using Btrieve or one of the third-party and/or shareware tools, modify FILE.DDF so that the path to the .DAT file is correct. This method is much more complicated, and not recommended for anyone not intimately familiar with Btrieve. ------------------------------------------------------------------------ Article Pending: Corrupt Error Message with Attached Btreive Table ------------------------------------------------------------------------ SYMPTOMS When you try to attach to a Btrieve table you receive the following error message: <tablename> is corrupted or isn't a Microsoft Access database. CAUSE When Microsoft Access attaches to a Btrieve table, it compares the information in the DDF files with the information in the tablename.DAT file. The most common cause of the above error message is a difference in the information for the indexes. RESOLUTION The user will need to redefine these indexes in a manner that Microsoft Access will not return the above error message. The most common solution is to redefine the index to be the exact length of the field that it is based upon. More Information: Example #1: ----------- The tablename.DAT file is defined with three columns: Column name Field Length Index Length ------------------------------------------ Firstname 10 <= Combined length of Lastname 10 <= 20 characters Company 25 Index1 is defined with a length of 20 characters which combines the firstname and lastname fields. Resolution: This index would need to be redefined into one multiply segment index, each with a defined length of 10 characters. Example #2: ----------- The tablename.DAT file is defined with three columns: Column name Field Length Index Length ------------------------------------------ Firstname 10 4 characters Lastname 10 Company 25 Index2 is defined with a length of 4 characters which is shorter than the defined length of the firstname field. Resolution: This index would need to be redefined from 4 characters in length to 10 characters in length, matching the size of the firstname field. Example #3: ----------- The tablename.DAT file is defined with three columns: Column name Field Length Index Length ------------------------------------------ Firstname 10 12 characters Lastname 10 Company 25 Index3 is defined with a length of 12 characters which is slightly longer than the defined length of the firstname field. Resolution: This index would need to be redefined from 12 characters in length to 10 characters in length, matching the size of the firstname field. Example #4: ----------- The tablename.DAT file is defined with three columns: Column name Length -------------------- Firstname - 10 Lastname - 10 Company - 25 Index4 is defined on a numeric byte range and not defined on any fields in the table. Resolution: This index would need to be removed completely and then redefined on an existing field and that has the same defined length as the field it is based upon. These types of indexes are valid to the Btrieve file format. Xtrieve from Novell simply ignores this type of index. Microsoft Access interrupts these indexes as being invalid and returns the above error message. How does a user go about changing the index? The user has several options: 1. Ask the vendor that created the Btrieve files to change the indexes to match the length of the defined fields. 2. Redefine the indexes with Btrieve to match the length of the defimed fields. 3. Redefine the indexes with Xtrieve to match the length of the defined fields. 4. Use any third party utility that is available to modify the Btrieve indexes. ------------------------------------------------------------------------ Article Pending: User is Unable to Attach to a Btrieve Database. ------------------------------------------------------------------------ SYMPTOMS When you try to attach to a Btrieve database you get an error message when you choose the FILE.DDF: Table <tablename> is exclusively locked. or Couldn't open file <filename>. CAUSE Whenever Microsoft Access tries to attach to a table, it needs to open the target table exclusively tempoarily. If the target Btrieve table is open by any other users at the same time that Microsoft Access tries to attach to the table, one of the above error messages will be returned. RESOLUTION The only workaround at this time is to find an available time when the target Btrieve table is not being used by any other users. Once Microsoft Access has attached to the Btrieve table, you should not receive the above error messages.